package dao;

import entity.Field;
import entity.Message;
import entity.Operator;
import entity.Passage;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CourseDao {
    private static String courseId = "15";
    private int num_every_page = 10;

    public CourseDao() { }

    private static Connection getConnection()  throws Exception{
        String driverName = "com.mysql.cj.jdbc.Driver";

        String url = "jdbc:mysql://localhost:3306/general_course?serverTimezone=GMT&useSSL=false&characterEncoding=utf8";
        String userName = "root";
        String password = "123456";

        Class.forName(driverName);

        return DriverManager.getConnection(url, userName, password);
    }
    private static Statement getStatement() throws Exception{
        Connection  connection = getConnection();
        return connection.createStatement();
    }

    public String getCourseName(String courseId){
        Statement stmt = null;
        try {
            String sql = "select name from course where courseID = " +courseId;
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            CourseDao.courseId = courseId;
            if(resultSet.next())
                return resultSet.getString(1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public String getCourseName(){
        return getCourseName(courseId);
    }

    public Operator getOperatorById(String operatorId){
        Statement stmt = null;
        try {
            String sql = "select * from operator where operatorID = " +operatorId;
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            if(resultSet.next())
                return new Operator(resultSet.getInt("operatorID"),
                                    resultSet.getString("oname"),
                                    resultSet.getString("account"),
                                    resultSet.getString("password"),
                                    resultSet.getInt("characters"),
                                    resultSet.getString("email"),
                                    resultSet.getString("sex"),
                                    resultSet.getString("photo"));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Field getFieldById(String fieldId){
        Statement stmt = null;
        try{
            String sql = "select fname from field where fieldID = " +fieldId;
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            if(resultSet.next())
                return new Field(Integer.valueOf(fieldId), resultSet.getString(1));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Passage getPassageByField(String field){
        Statement stmt = null;
        try {
            String sql = "select * from passage where course = " + courseId + " and field = " + field + " and status = 2";
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            if(resultSet.next())
                return createPassage(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public Passage getPassageById(String id){
        Statement stmt = null;
        try {
            stmt = getStatement();
            String sql = "select * from passage where passageID = " + id ;
            ResultSet resultSet = stmt.executeQuery(sql);
            Passage passage = null;
            if(resultSet.next())
                passage = createPassage(resultSet);
            String sql2 = "update passage set count = " + (passage.getCount() + 1) + " where passageID = " + id;
            stmt.execute(sql2);
            passage.setCount(passage.getCount() + 1);
            return passage;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public List<Passage> getFieldPassages(String field, int page){
        Statement stmt = null;
        List<Passage> passages = new ArrayList<>();
        try{
            String sql = "select * from passage where course = " + courseId + " and field = " + field + " and status = 2"
                    + " order by roof DESC" + " limit " + (page-1)*num_every_page + "," + num_every_page ;
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while(resultSet.next()){
                passages.add(createPassage(resultSet));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return passages;
    }
    private Passage createPassage(ResultSet resultSet) throws SQLException {
        return new Passage(resultSet.getInt("passageID"),
                resultSet.getString("title"),
                resultSet.getString("content"),
                resultSet.getDate("createDate").toString(),
                resultSet.getInt("count"),
                resultSet.getInt("operator"),
                resultSet.getInt("field"),
                resultSet.getInt("course"),
                resultSet.getInt("status"),
                resultSet.getInt("roof"));
    }


    public List<Passage> getSearchPassages(String search, int page) {
        String sql = "select * from passage where course = " + courseId + " and status = 2" +
                " and title like \"%" + search  +"%\"" + " order by roof DESC" +
                " limit " + (page-1)*num_every_page + "," + num_every_page ;
        return searchPassages(sql);
    }
    public List<Passage> getSearchPassages(String search, String field, int page) {
        String sql = "select * from passage where course = " + courseId + " and field = " + field + " and status = 2" +
                " and title like \"%" + search  +"%\"" + " order by roof DESC" +
                " limit " + (page-1)*num_every_page + "," + num_every_page ;
        return searchPassages(sql);
    }
    private List<Passage> searchPassages(String sql){
        Statement stmt = null;
        List<Passage> passages = new ArrayList<>();
        try{
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while(resultSet.next()){
                passages.add(createPassage(resultSet));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return passages;
    }

    public List<Message> getMessageList(int page){
        Statement stmt = null;
        List<Message> messages = new ArrayList<>();
        try{
            String sql = "select * from message where course = " + courseId + " and status = 2" + " limit "
                    + (page-1)*num_every_page + "," + num_every_page ;
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while(resultSet.next()){
                messages.add(new Message(resultSet.getInt("messageId"),
                        resultSet.getString("content"),
                        resultSet.getDate("createDate").toString(),
                        resultSet.getInt("course"),
                        resultSet.getInt("status")));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return messages;
    }

    public void addMessage(String message){
        Statement stmt = null;
        try{
            Date date = new Date(System.currentTimeMillis());
            Timestamp timeStamp = new Timestamp(date.getTime());
            String sql = "insert into message(messageId,content,createDate,course,status)" +
                    " values(" + (getNumberOfAllMessages()+1) + ", \"" + message + "\", \"" +
                     timeStamp + "\", " + courseId + ", " + 1 + ")";
            stmt = getStatement();
            stmt.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int getNumberOfMessages(int status){
        String sql = "select COUNT(*) from message where course = " + courseId + " and status = " + status;
        return count(sql);
    }
    public int getNumberOfAllMessages(){
        String sql = "select COUNT(*) from message where course = " + courseId;
        return count(sql);
    }
    public int getNumberOfOperator(){
        String sql = "select COUNT(*) from operator ";
        return count(sql);
    }
    public int getNumberOfFieldPassages(String field, int status){
        String sql = "select COUNT(*) from passage where course = " + courseId + " and field = " + field+ " and status = " + status;
        return count(sql);
    }
    public int getNumberOfSearchPassages(String search){
        String sql = "select COUNT(*) from passage where course = " + courseId + " and status = 2" +
                " and title like \"%" + search  +"%\"";
        return count(sql);
    }
    public int getNumberOfSearchPassages(String search, String fieldId) {
        String sql = "select COUNT(*) from passage where course = " + courseId + " and field = " + fieldId + " and status = 2" +
                " and title like \"%" + search  +"%\"";
        return count(sql);
    }
    public int getNumberOfPassages(int status){
        String sql = "select COUNT(*) from passage where status = " + status;
        return count(sql);
    }
    public int getNumberOfPassageByTT(String title, int status, String begin, String end) {
        String sql = "select COUNT(*) from passage where status =" + status + " and " +
                " title like \'%"+title+"%\' and createDate between \'" + begin + "\' and \'" + end + "\'";
        System.out.println(sql);
        return count(sql);
    }
    public int getNumberOfPassageByFT(int field, int status, String begin, String end){
        String sql = "select COUNT(*) from passage where status = " + status + " and field = " + field +
                " and createDate between \'" + begin + "\' and \'" + end +"\'";
        System.out.println(sql);
        return count(sql);
    }

    private int count(String sql){
        Statement stmt = null;
        try{
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            if(resultSet.next())
                return resultSet.getInt(1);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    public String getCharacterNameById(int characterId) {
        Statement stmt = null;
        try {
            String sql = "select cname from characters where characterID = " +characterId;
            stmt = getStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            if(resultSet.next())
                return resultSet.getString(1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void passwordChange(String operatorId, String newPassword){
        Statement stmt = null;
        try {
            String sql = "update operator set password=\"" + newPassword + "\"" + " where operatorId=" + operatorId;
            stmt = getStatement();
            stmt.execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateCharacter(String operatorId, String character){
        Statement stmt = null;
        try {
            String sql = "update operator set characters=" + character + " where operatorId=" + operatorId;
            stmt = getStatement();
            stmt.execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateModular(String characterId, String permissions) {
        String[] permissionlist = permissions.split(",");
        Statement stmt = null;
        try {
            String sql = "delete from permission where characters = " + characterId;
            stmt = getStatement();
            stmt.execute(sql);

            String sql2 = "select permissionID as id from permission order by id DESC";
            ResultSet resultSet = stmt.executeQuery(sql2);
            Integer largest = 0;
            if(resultSet.next())
                largest = resultSet.getInt(1);

            for(int i = 0; i < permissionlist.length; i++){
                String sql3 = "insert into permission(permissionID,characters,modular)" +
                        " values(" + (++largest) + ", " + characterId + ", " + permissionlist[i] + ")";
                stmt.execute(sql3);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
